package es.udc.emarketapp.emarket.model.purchase.dao;

import java.util.Calendar;
import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;

import es.udc.emarketapp.emarket.model.purchase.vo.PurchaseVO;
import es.udc.fbellas.j2ee.util.exceptions.InstanceNotFoundException;
import es.udc.fbellas.j2ee.util.exceptions.InternalErrorException;
import es.udc.fbellas.j2ee.util.sql.GeneralOperations;

public abstract class AbstractSQLPurchaseDAO implements SQLPurchaseDAO {

    protected AbstractSQLPurchaseDAO() {
    }

    public boolean exists(Connection connection, Long nCompra)
            throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT n_compra FROM compra "
                    + "WHERE n_compra = ?";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            preparedStatement.setLong(1, nCompra.longValue());

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            return resultSet.next();

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

    }// exists


    public PurchaseVO findById(Connection connection, Long nCompra)
            throws InstanceNotFoundException, InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT id_comprador, fecha, "
                    + "valor, forma_pago FROM compra  WHERE n_compra = ? "
                    + "ORDER BY n_compra DESC";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            preparedStatement.setLong(1, nCompra.longValue());

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            if (!resultSet.next()) {
                throw new InstanceNotFoundException(nCompra,
                        PurchaseVO.class.getName());
            }

            // Get results.
            int i = 1;
            String idComprador = resultSet.getString(i++);
            Calendar fecha = Calendar.getInstance();
            fecha.setTimeInMillis((resultSet.getTimestamp(i++)).getTime());
 

            double valor = resultSet.getDouble(i++);
            String formaPago = resultSet.getString(i++);

            // Return the value object.
            return new PurchaseVO(nCompra, idComprador, fecha, valor, formaPago);

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

    }// findById

    public PurchaseVO findLastByClient(Connection connection, String idComprador)
            throws InstanceNotFoundException, InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT n_compra, fecha, "
                    + "valor, forma_pago FROM compra WHERE n_compra = "
                    + "(SELECT MAX(n_compra) FROM compra where id_comprador = ?)";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            preparedStatement.setString(1, idComprador);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            if (!resultSet.next()) {
                throw new InstanceNotFoundException(idComprador,
                        PurchaseVO.class.getName());
            }

            // Get results.
            int i = 1;
            Long nCompra = new Long(resultSet.getLong(i++));
            Calendar fecha = Calendar.getInstance();
            fecha.setTimeInMillis((resultSet.getTimestamp(i++)).getTime());
            double valor = resultSet.getDouble(i++);
            String formaPago = resultSet.getString(i++);

            // Return the value object.
            return new PurchaseVO(nCompra, idComprador, fecha, valor, formaPago);

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

    }// findLastByClient

    public List<PurchaseVO> findByClient(Connection connection,
            String idComprador, int startIndex, int count)
            throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT n_compra, fecha, valor, forma_pago "
                    + "FROM compra WHERE id_comprador = ? ORDER BY n_compra DESC";

            preparedStatement = connection.prepareStatement(queryString,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            preparedStatement.setString(1, idComprador);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            // Read objects.
            List<PurchaseVO> purchaseVOs = new ArrayList<PurchaseVO>();
            int currentCount = 0;

            if ((startIndex >= 1) && resultSet.absolute(startIndex)) {
                do {
                    int i = 1;
                    Long nCompra = new Long(resultSet.getLong(i++));
                    Calendar fecha = Calendar.getInstance();
                    fecha.setTimeInMillis((resultSet.getTimestamp(i++)).getTime());
                    double valor = resultSet.getDouble(i++);
                    String formaPago = resultSet.getString(i++);

                    purchaseVOs.add(new PurchaseVO(nCompra, idComprador, fecha,
                            valor, formaPago));

                    currentCount++;

                } while (resultSet.next() && (currentCount < count));
            }// if

            // Return value objects.
            return purchaseVOs;

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

    }// findByCLient

    public List<PurchaseVO> findAll(Connection connection, int startIndex,
            int count) throws InternalErrorException {

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {

            // Create "preparedStatement".
            String queryString = "SELECT n_compra, id_comprador, fecha, "
                    + "valor, forma_pago FROM compra ORDER BY n_compra DESC";

            preparedStatement = connection.prepareStatement(queryString,
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            // Execute query.
            resultSet = preparedStatement.executeQuery();

            // Read objects.
            List<PurchaseVO> purchaseVOs = new ArrayList<PurchaseVO>();
            int currentCount = 0;

            if ((startIndex >= 1) && resultSet.absolute(startIndex)) {
                do {
                    int i = 1;
                    Long nCompra = new Long(resultSet.getLong(i++));
                    String idComprador = resultSet.getString(i++);
                    Calendar fecha = Calendar.getInstance();
                    fecha.setTimeInMillis((resultSet.getTimestamp(i++)).getTime());
                    double valor = resultSet.getDouble(i++);
                    String formaPago = resultSet.getString(i++);

                    purchaseVOs.add(new PurchaseVO(nCompra, idComprador, fecha,
                            valor, formaPago));

                    currentCount++;

                } while (resultSet.next() && (currentCount < count));
            }// if

            // Return value objects.
            return purchaseVOs;

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeResultSet(resultSet);
            GeneralOperations.closeStatement(preparedStatement);
        }

    }// findAll

    public void updateValue(Connection connection, Long nCompra, double valor)
            throws InstanceNotFoundException, InternalErrorException {

        PreparedStatement preparedStatement = null;

        try {

            // Create "preparedStatement".
            String queryString = "UPDATE compra "
                    + "SET valor = ? WHERE n_compra = ? ";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            int i = 1;
            preparedStatement.setDouble(i++, valor);
            preparedStatement.setLong(i++, nCompra);

            // Execute query.
            int updatedRows = preparedStatement.executeUpdate();

            if (updatedRows == 0) {
                throw new InstanceNotFoundException(nCompra.longValue(), PurchaseVO.class.getName());
            }

            if (updatedRows > 1) {
                throw new SQLException("Fila duplicada para el n_compra = '"
                        + nCompra.longValue()
                        + "' en la tabla 'Compra'");
            }

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeStatement(preparedStatement);
        }

    }// updateValue

    public void update(Connection connection, PurchaseVO purchaseVO)
            throws InstanceNotFoundException, InternalErrorException {
        PreparedStatement preparedStatement = null;

        try {

            // Create "preparedStatement".
            String queryString = "UPDATE compra "
                    + "SET id_comprador = ?, fecha = ?, valor = ?, "
                    + "forma_pago = ? WHERE n_compra = ? ";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            int i = 1;
            preparedStatement.setString(i++, purchaseVO.getIdComprador());
            preparedStatement.setTimestamp(i++, new Timestamp(Calendar.getInstance().getTime().getTime()));
            preparedStatement.setDouble(i++, purchaseVO.getValor());
            preparedStatement.setString(i++, purchaseVO.getFormaPago());
            preparedStatement.setLong(i++, purchaseVO.getnCompra());

            // Execute query.
            int updatedRows = preparedStatement.executeUpdate();

            if (updatedRows == 0) {
                throw new InstanceNotFoundException(purchaseVO.getnCompra().longValue(), PurchaseVO.class.getName());
            }

            if (updatedRows > 1) {
                throw new SQLException("Fila duplicada para el n_compra = '"
                        + purchaseVO.getnCompra().longValue()
                        + "' en la tabla 'Compra'");
            }

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeStatement(preparedStatement);
        }

    }// update

    public void remove(Connection connection, Long nCompra)
            throws InstanceNotFoundException, InternalErrorException {
        PreparedStatement preparedStatement = null;

        try {

            // Create "preparedStatement".
            String queryString = "DELETE FROM compra WHERE " + "n_compra = ?";
            preparedStatement = connection.prepareStatement(queryString);

            // Fill "preparedStatement".
            preparedStatement.setLong(1, nCompra.longValue());

            // Execute query.
            int removedRows = preparedStatement.executeUpdate();

            if (removedRows == 0) {
                throw new InstanceNotFoundException(nCompra.longValue(),
                        PurchaseVO.class.getName());
            }

        } catch (SQLException e) {
            throw new InternalErrorException(e);
        } finally {
            GeneralOperations.closeStatement(preparedStatement);
        }

    }// remove
}
